package com.oscarmovie.dao;

import java.math.BigDecimal;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oscarmovie.Constants;
import com.oscarmovie.MovieRelShipUtil;
import com.oscarmovie.model.Movie;
import com.oscarmovie.model.Paging;

public class MovieDaoImpl extends HibernateDaoSupport implements MovieDao {

	private CacheDao cacheDao;
	
	
	/**
	 * @param cacheDao the cacheDao to set
	 */
	public void setCacheDao(CacheDao cacheDao) {
		this.cacheDao = cacheDao;
	}

	
	public void save(Movie movie) {

		getSession().saveOrUpdate(movie);
		//getSession().flush();
	}

	public Movie load(int id) {
		Movie movie = (Movie) getSession().load(Movie.class, id);
		return movie;
	}

	public List<Movie> list(Paging paging) {
		paging.setTotalCount((int) count());
		Query query = getSession().createQuery("from Movie movie order by movie.calendar.id desc");
		query.setCacheable(true);
		query.setFirstResult(paging.getCurrentRow()-1);
		query.setMaxResults(paging.getPageSize());
		List<Movie> list = (List<Movie>) query.list();
		return list;
	}
	
	
	public List<Movie> list(Paging paging, Movie movie) {
		paging.setTotalCount((int) count());
		Query query = getSession().createQuery("from Movie movie where (movie.name like :name or movie.nameCn like :nameCn) order by movie.calendar.id desc");
		query.setCacheable(true);
		query.setString("name", "%"+movie.getName()+"%");
		query.setString("nameCn", "%"+movie.getNameCn()+"%");
		query.setFirstResult(paging.getCurrentRow()-1);
		query.setMaxResults(paging.getPageSize());
		List<Movie> list = (List<Movie>) query.list();
		return list;
	}
	
	public List<Movie> loadAll(int calendarId, int categoryId, int countryId, int awardId, int personId, int type, Paging paging) {
		paging.setTotalCount((int) loadAllCount(calendarId, categoryId, countryId, awardId, personId, type));
		
		String sql = loadAllSql(calendarId,categoryId,countryId,awardId,personId, type);
		sql ="select * from movie where id in ("+sql+") order by calendar_id desc";		
		
		sql = paging(sql,paging.getCurrentRow(),paging.getPageSize());
		
		Query query = getSession().createSQLQuery(sql).addEntity(Movie.class).setCacheable(true);
		List<Movie> list = query.list();
		
		return list;
	}
	
	
	public List<Movie> loadMostAwarded(int count) {
		Query query = getSession().createQuery("from Movie movie order by movie.statistics.awarded desc");
		query.setCacheable(true);
		query.setFirstResult(0);
		query.setMaxResults(count);
		List<Movie> list = (List<Movie>) query.list();
		return list;
	}
	
	
	public List<Movie> loadMostScore(int count) {
		Query query = getSession().createQuery("from Movie movie order by movie.statistics.score desc");
		query.setCacheable(true);
		query.setFirstResult(0);
		query.setMaxResults(count);
		List<Movie> list = (List<Movie>) query.list();
		return list;
	}

	
	public List<Movie> loadMostWatched(int count) {
		Query query = getSession().createQuery("from Movie movie order by movie.statistics.watched desc");
		query.setCacheable(true);
		query.setFirstResult(0);
		query.setMaxResults(count);
		List<Movie> list = (List<Movie>) query.list();
		return list;
	}

	
	public List<Movie> loadMostWanted(int count) {
		Query query = getSession().createQuery("from Movie movie order by movie.statistics.wanted desc");
		query.setCacheable(true);
		query.setFirstResult(0);
		query.setMaxResults(count);
		List<Movie> list = (List<Movie>) query.list();
		return list;
	}
	
	/*
	 * private methods
	 *
	 */
	private String paging(String sql, int currentRow, int pageSize){
		if (Constants.DB_TYPE.equals(Constants.DB_TYPE_ORACLE)) {
			sql = "select * from ( select tb.*, rownum rn from (" + sql + " ) tb ) where rn >=" + currentRow + " and rn <" + (currentRow + pageSize);
		} else if (Constants.DB_TYPE.equals(Constants.DB_TYPE_MYSQL)) {
			if(currentRow>0) currentRow--;
			sql = sql + " limit " + currentRow + ", " + (pageSize);
		}
		return sql;
	}
	
	private String loadAllSql(int calendarId, int categoryId, int countryId, int awardId, int personId, int type){
		
		return cacheDao.getMovieIdString(calendarId, categoryId, countryId,awardId, personId, type);		
	}		
	
	private long count() {
		Query query = getSession().createQuery("select count(*) from Movie");
		query.setCacheable(true);
		Long count = (Long) query.uniqueResult();
		return count;
	}
	
	private long loadAllCount(int calendarId, int categoryId, int countryId, int awardId, int personId, int type) {
		String sql = loadAllSql(calendarId,categoryId,countryId,awardId,personId,type);
		sql ="select count(*) as totalCount from movie where id in ("+sql+")";						
		Object count = getSession().createSQLQuery(sql).addScalar("totalCount", Hibernate.INTEGER).setCacheable(true).uniqueResult();
		
		if (count instanceof BigDecimal) {
		   return ((BigDecimal)count).longValue();
		} else { 
		   return ((Integer)count).longValue();
		}
			 
	}
	

}
